package yangsw87.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class J23_MemberDAO implements J23_MemberService {

	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "exer";
	private static final String PWD = "asdf";

	private static final String insertSQL = " insert into member(userid, jumin, pwd, zip, address, tel, mp, paycode) "
			+ " values(?, ?, ?, ?, ?, ?, ?, ?) ";
	private static final String selectSQL = " select userid, jumin, mp from member order by userid ";
	private static final String selectOneSQL = " select * from member where userid=? order by userid ";
	private static final String updateSQL = " update member set pwd=?, zip=?, address=?, tel=?, mp=?, paycode=? where userid=? ";
	private static final String deleteSQL = " delete from member where userid=? ";

	@Override
	public int insertMember(J23_MemberVO mvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);

			ps.setString(1, mvo.getUserid());
			ps.setString(2, mvo.getJumin());
			ps.setString(3, mvo.getPwd());
			ps.setString(4, mvo.getZip());
			ps.setString(5, mvo.getAddress());
			ps.setString(6, mvo.getTel());
			ps.setString(7, mvo.getMp());
			ps.setString(8, mvo.getPaycode());

			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception ex) {
				}
			}

		}
		return cnt;
	}

	@Override
	public ArrayList<J23_MemberVO> readMember() {
		ArrayList<J23_MemberVO> data = new ArrayList<>();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();

			while (rs.next()) {
				J23_MemberVO mvo = new J23_MemberVO(rs.getString(1),
						rs.getString(2), null, null, null, null,
						rs.getString(3), null);
				// 결과집합 rs에 저장된 모든 데이터 중
				// 레코드 단위(행)로 하나씩 읽어와서
				// J23_Member 클래스에 저장하고
				// ArrayList인 data에 추가함
				data.add(mvo);
			} // while

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception ex) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}

		return data;
	}

	@Override
	public J23_MemberVO readOneMember(String userid) {
		J23_MemberVO data = null;

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectOneSQL);

			ps.setString(1, userid);
			rs = ps.executeQuery();

			while (rs.next()) {
				data = new J23_MemberVO(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7), rs.getString(8));

			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception ex) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return data;
	}

	@Override
	public int updateMember(J23_MemberVO mvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);

			ps.setString(1, mvo.getPwd());
			ps.setString(2, mvo.getZip());
			ps.setString(3, mvo.getAddress());
			ps.setString(4, mvo.getTel());
			ps.setString(5, mvo.getMp());
			ps.setString(6, mvo.getPaycode());
			ps.setString(7, mvo.getUserid());

			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (Exception ex) {
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (Exception ex) {
					}
				}
			}
		}
		return cnt;
	}

	@Override
	public int deleteMember(String userid) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);

			ps.setString(1, userid);

			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return cnt;
	}

}
